-- STANDARD SCRIPT
-- INSERT DATA IN batch_recon_t
--
--


------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO ACCOUNT TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION load_batch_recon_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_data_flag varchar(1);
  v_seqnum integer;
  v_batch_recon_received integer;
  v_hccurs refcursor;
  v_hccurs2 refcursor;
  v_row batch_recon_t%ROWTYPE;
  v_row2 batch_recon_t%ROWTYPE;

BEGIN


-- GETTING PARAMETERS
SELECT INTO v_batch_run_date,v_cust_id,v_data_flag our_run_date,cust_id,data_flag from temp_batch_recon_t limit 1;

IF (v_data_flag = 'U') THEN

      SELECT INTO v_batch_run_date validated_date FROM  temp_batch_recon_t limit 1;

ELSIF (v_data_flag = 'E') THEN

      SELECT INTO v_batch_run_date our_run_date FROM  temp_batch_recon_t limit 1;

ELSIF (v_data_flag = 'R') THEN

      SELECT INTO v_batch_run_date recon_sent_date FROM  temp_batch_recon_t limit 1;

END IF;



-- INITIALIZING SEQNUM to max(SEQNUM in database) + 1

SELECT INTO v_seqnum max(seqnum) from recon_t ;

IF    v_seqnum IS NULL THEN v_seqnum := 0;
END IF;


-- SCRATCH batch_recon_t TABLES
TRUNCATE batch_recon_t;



-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-- INSERT RECORDS INTO batch_recon_t    
-- TWO CASES DEPENDING ON WHETHER data_flag = 'M' (come from insurance2 process) OR 'C' (client recon file)

PERFORM ml_upd_batch_status_recon_new_f(v_cust_id,v_batch_run_date,1,2,'START INSERT IN batch_recon_t');
RAISE NOTICE 'START INSERT INTO batch_recon_t TABLE';


IF (v_data_flag = 'M') THEN

	OPEN v_hccurs FOR SELECT * FROM temp_batch_recon_t;

ELSIF (v_data_flag = 'C') THEN

        OPEN v_hccurs FOR SELECT
          
           recon.data_flag              ,
           recon.seqnum                 ,
           batch.recon_ind              ,
           batch.recon_exclude          ,
           batch.recon_full_scenario    ,
           batch.customer_type          ,
           batch.processing_type        ,
           recon.emdeon_ind             ,
           recon.cust_id                ,
           recon.hid                    ,
           recon.acctnum                ,
           recon.lname                  ,
           recon.client_field1          ,
           recon.client_field2          ,
           recon.client_field3          ,
           recon.client_date1           ,
           recon.pat_proc_type          ,
           recon.pat_type               ,
           recon.original_run_date::timestamp,
           recon.first_delivery_date    ,
           recon.our_run_date           ,
           recon.service_date_begin     ,
           recon.service_date_end       ,
           recon.charges                ,
           recon.balance                ,
           recon.insurance_desc         ,
           recon.carrier_code           ,
           recon.plan_type              ,
           recon.date_billed            ,
           recon.date_rejected          ,
           recon.prior_ins_date         ,
           recon.paid_date              ,
           recon.amount_paid            ,
           recon.note                   ,
           recon.medlytix_note          ,
           recon.validate_status        ,
           recon.validated_date         ,
           recon.multiple_row           ,
           recon.warning_doublepay      ,
           recon.user_id                ,
           recon.recon_sent_date        ,
           recon.medlytix_ins_type       

	FROM

		(SELECT 
                        a.data_flag              as data_flag, 
                        a.seqnum                 as seqnum, 
                        a.recon_ind              as recon_ind,
                        a.recon_exclude          as recon_exclude,
                        a.recon_full_scenario    as recon_full_scenario,
                        a.customer_type          as customer_type,
                        a.processing_type        as processing_type,
                        a.emdeon_ind             as emdeon_ind,
                        a.cust_id                as cust_id,
                        a.hid                    as hid,
                        a.acctnum                as acctnum,
                        b.lname                  as lname, 
                        b.client_field1          as client_field1,
                        b.client_field2          as client_field2,
                        b.client_field3          as client_field3,
                        b.client_date1           as client_date1,
                        b.pat_proc_type          as pat_proc_type,
                        b.pat_type               as pat_type,
                        b.our_run_date           as original_run_date,
                        b.file_date              as first_delivery_date,
                        a.our_run_date           as our_run_date,
                        b.service_date_begin     as service_date_begin,
                        b.service_date_end       as service_date_end,
                        b.charges                as charges,
                        b.balance                as balance,
                        b.insurance_desc         as insurance_desc,
                        a.carrier_code           as carrier_code,
                        b.plan_type              as plan_type,
                        a.date_billed            as date_billed,
                        a.date_rejected          as date_rejected,
                        a.prior_ins_date         as prior_ins_date,
                        a.paid_date              as paid_date,
                        a.amount_paid            as amount_paid,
                        a.note                   as note,
                        a.medlytix_note          as medlytix_note,
                        a.validate_status        as validate_status,
                        a.validated_date         as validated_date,
                        'N'                      as multiple_row,
                        'N'                      as warning_doublepay,
                        a.user_id                as user_id,
                        a.recon_sent_date        as recon_sent_date,
                        b.medlytix_ins_type      as medlytix_ins_type
                  
		FROM
                   	(SELECT * FROM temp_batch_recon_t) as a 

			JOIN

             		(SELECT cust_id,
                     		hid,
                     		acctnum,
                     		carrier_code,
                     		lname,
                     		client_field1,
                     		client_field2,
                     		client_field3,
                     		client_date1,
                     		pat_proc_type,
                     		pat_type,
                     		our_run_date,
                     		file_date,
                     		service_date_begin,
                     		service_date_end,
                     		charges,
                     		balance,
                     		insurance_desc,
                     		plan_type,
                                medlytix_ins_type 
             		FROM
                     		insurance2_results_t

             		) as b 

             		ON

             		(a.cust_id=b.cust_id and a.hid=b.hid and a.acctnum=b.acctnum and a.carrier_code=b.carrier_code)

		) as recon 

                JOIN

                (
                        SELECT distinct on (cust_id,last_run_date) 
                                           cust_id,last_run_date,recon_ind,recon_exclude,recon_full_scenario,customer_type,processing_type
                        FROM batch_status_insurance1_t

                 ) as batch

                ON

                (recon.cust_id=batch.cust_id) AND (recon.original_run_date=batch.last_run_date)

        ;
END IF;

        
IF v_data_flag in ('M','C') THEN

  	LOOP
    		FETCH v_hccurs INTO v_row;
    		IF NOT FOUND THEN
      		EXIT;
    		END IF;
    		v_seqnum := v_seqnum + 1;

                IF v_row.cust_id >= 5000 THEN
                   v_row.emdeon_ind := 'Y';
                ELSE
                   v_row.emdeon_ind := 'N';
                END IF;

    		INSERT INTO batch_recon_t
    			(
  			data_flag                 ,
  			seqnum                    ,
  			recon_ind                 , 
  			recon_exclude             ,
                        recon_full_scenario       ,
  			customer_type             ,
  			processing_type           ,
  			emdeon_ind                ,
  			cust_id                   ,
  			hid                       ,
  			acctnum                   ,
  			lname                     ,
  			client_field1             ,
  			client_field2             ,
  			client_field3             ,
  			client_date1              ,
  			pat_proc_type             ,
  			pat_type                  ,
  			original_run_date         ,
  			first_delivery_date       ,
  			our_run_date              ,
  			service_date_begin        ,
  			service_date_end          ,
  			charges                   ,
  			balance                   ,
  			insurance_desc            ,
  			carrier_code              ,
  			plan_type                 ,
  			date_billed               ,
  			date_rejected             ,
  			prior_ins_date            ,
  			paid_date                 ,
  			amount_paid               ,
  			note                      ,
                        medlytix_note             ,
  			validate_status           ,
  			validated_date            ,
  			multiple_row              ,
                        warning_doublepay         ,
  			user_id                   ,
  			recon_sent_date           ,
                        medlytix_ins_type    
    			)
    			VALUES
    			(
                        v_row.data_flag           ,
                        v_seqnum                  ,
                        v_row.recon_ind           ,
                        v_row.recon_exclude       ,
                        v_row.recon_full_scenario ,
                        v_row.customer_type       ,
                        v_row.processing_type     ,
                        v_row.emdeon_ind          ,
                        v_row.cust_id             ,
                        v_row.hid                 ,
                        v_row.acctnum             ,
                        v_row.lname               ,
                        v_row.client_field1       ,
                        v_row.client_field2       ,
                        v_row.client_field3       ,
                        v_row.client_date1        ,
                        v_row.pat_proc_type       ,
                        v_row.pat_type            ,
                        v_row.original_run_date   ,
                        v_row.first_delivery_date ,
                        v_row.our_run_date        ,
                        v_row.service_date_begin  ,
                        v_row.service_date_end    ,
                        v_row.charges             ,
                        v_row.balance             ,
                        v_row.insurance_desc      ,
                        v_row.carrier_code        ,
                        v_row.plan_type           ,
                        v_row.date_billed         ,
                        v_row.date_rejected       ,
                        v_row.prior_ins_date      ,
                        v_row.paid_date           ,
                        v_row.amount_paid         ,
                        v_row.note                ,
                        v_row.medlytix_note       ,
                        v_row.validate_status     ,
                        v_row.validated_date      ,
                        v_row.multiple_row        ,
                        v_row.warning_doublepay   ,
                        v_row.user_id             ,
                        v_row.recon_sent_date     ,
                        v_row.medlytix_ins_type
    			)
    		;


	END LOOP;

END IF;  

RAISE NOTICE 'DONE INSERT INTO batch_recon_t TABLE ';



---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-- PUTTING SOME LOGIC IN PLACE AND UPDATE validate_status FLAG 
-- A) RECORDS TYPE C WHICH ARE STILL OPEN (AO,VO) AND 25 DAYS IN.  IN THIS CASE WE CREATE A NEW RECORD WITH TYPE C) BUT WITH validate_status = NR
-- B) IF WE GET SAME AMOUNT PAID FOR SAME ACCOUNT ACROSS DIFFERENT CARRIER WE REMOVE ONE BUT PUT A NOTE IN OTHER ROWS.
-- C) UPDATE validate_status


IF (v_data_flag = 'C') THEN

        -- PROCESS A)
        RAISE NOTICE 'START INSERT OF MISSING RECON RECORD INTO batch_recon_t TABLE IF ANY';


        --SELECT INTO v_seqnum max(seqnum) from batch_recon_t ;

        RAISE NOTICE  'SEQNUM IS : %', v_seqnum;

        OPEN v_hccurs2 FOR SELECT

         'C' as data_flag       ,
         nr.seqnum              ,
         nr.recon_ind           ,
         nr.recon_exclude       ,
         nr.recon_full_scenario ,
         nr.customer_type       ,
         nr.processing_type     ,
         nr.emdeon_ind          ,
         nr.cust_id             ,
         nr.hid                 ,
         nr.acctnum             ,
         nr.lname               ,
         nr.client_field1       ,
         nr.client_field2       ,
         nr.client_field3       ,
         nr.client_date1        ,
         nr.pat_proc_type       ,
         nr.pat_type            ,
         nr.original_run_date   ,
         nr.first_delivery_date ,
         nr.our_run_date        ,
         nr.service_date_begin  ,
         nr.service_date_end    ,
         nr.charges             ,
         nr.balance             ,
         nr.insurance_desc      ,
         nr.carrier_code        ,
         nr.plan_type           ,
         nr.date_billed         ,
         nr.date_rejected       ,
         nr.prior_ins_date      ,
         nr.paid_date           ,
         nr.amount_paid         ,
         nr.note                ,
         nr.medlytix_note       ,
         'NR' as validate_status,
         nr.validated_date      ,
         nr.multiple_row        ,
         nr.warning_doublepay   ,
         nr.user_id             ,
         nr.recon_sent_date     ,
         nr.medlytix_ins_type
           
       FROM
        ( SELECT 
            distinct on (b.cust_id,b.hid,b.acctnum,b.carrier_code)
            b.*,
            a.cust_id as cst
          FROM
             ( SELECT * FROM batch_recon_t ) as a 
               RIGHT JOIN 
             ( 
               SELECT
               *
               FROM
                (
                 
                 SELECT distinct on (cust_id,hid,acctnum,carrier_code) *                        
                 FROM recon_t 
                 WHERE cust_id = v_cust_id and (data_flag = 'C' or  ( data_flag = 'M' and ml_ddiff_f('d',our_run_date,'now') >= 45) ) 
                 ORDER BY cust_id,hid,acctnum,carrier_code,seqnum DESC
                ) as temp

                -- NOTE WE SELECT HERE ONLY acctnum WHERE ONLY RECORD PRESENT IN recon_t IS M RECORD OLDER THAN 45 DAYS
                --      OR C RECORD OPEN GREATER THAN 25 DAYS 
                WHERE (temp.data_flag = 'C' and temp.validate_status in ('AO','VO') and ml_ddiff_f('d',temp.our_run_date,'now') >= 25)
                      OR
                       temp.data_flag = 'M' 

             ) as b
               ON
             ( a.cust_id = b.cust_id AND a.hid = b.hid AND a.acctnum = b.acctnum AND a.carrier_code = b.carrier_code)

        ) as nr

       WHERE nr.cst IS NULL
       ;
 
 
       LOOP
                FETCH v_hccurs2 INTO v_row2;
                IF NOT FOUND THEN
                   EXIT;
                ELSE
                   v_seqnum := v_seqnum + 1;

                   INSERT INTO batch_recon_t
                        (
                        data_flag                 ,
                        seqnum                    ,
                        recon_ind                 ,
                        recon_exclude             ,
                        recon_full_scenario       ,
                        customer_type             ,
                        processing_type           ,
                        emdeon_ind                ,
                        cust_id                   ,
                        hid                       ,
                        acctnum                   ,
                        lname                     ,
                        client_field1             ,
                        client_field2             ,
                        client_field3             ,
                        client_date1              ,
                        pat_proc_type             ,
                        pat_type                  ,
                        original_run_date         ,
                        first_delivery_date       ,
                        our_run_date              ,
                        service_date_begin        ,
                        service_date_end          ,
                        charges                   ,
                        balance                   ,
                        insurance_desc            ,
                        carrier_code              ,
                        plan_type                 ,
                        date_billed               ,
                        date_rejected             ,
                        prior_ins_date            ,
                        paid_date                 ,
                        amount_paid               ,
                        note                      ,
                        medlytix_note             ,
                        validate_status           ,
                        validated_date            ,
                        multiple_row              ,
                        warning_doublepay         ,
                        user_id                   ,
                        recon_sent_date           ,
                        medlytix_ins_type
                        )
                        VALUES
                        (
                        v_row2.data_flag           ,
                        v_seqnum                  ,
                        v_row2.recon_ind           ,
                        v_row2.recon_exclude       ,
                        v_row2.recon_full_scenario ,
                        v_row2.customer_type       ,
                        v_row2.processing_type     ,
                        v_row2.emdeon_ind          ,
                        v_row2.cust_id             ,
                        v_row2.hid                 ,
                        v_row2.acctnum             ,
                        v_row2.lname               ,
                        v_row2.client_field1       ,
                        v_row2.client_field2       ,
                        v_row2.client_field3       ,
                        v_row2.client_date1        ,
                        v_row2.pat_proc_type       ,
                        v_row2.pat_type            ,
                        v_row2.original_run_date   ,
                        v_row2.first_delivery_date ,
                        v_batch_run_date           ,
                        v_row2.service_date_begin  ,
                        v_row2.service_date_end    ,
                        v_row2.charges             ,
                        v_row2.balance             ,
                        v_row2.insurance_desc      ,
                        v_row2.carrier_code        ,
                        v_row2.plan_type           ,
                        v_row2.date_billed         ,
                        v_row2.date_rejected       ,
                        v_row2.prior_ins_date      ,
                        v_row2.paid_date           ,
                        v_row2.amount_paid         ,
                        v_row2.note                ,
                        v_row2.medlytix_note       ,
                        v_row2.validate_status     ,
                        v_row2.validated_date      ,
                        v_row2.multiple_row        ,
                        v_row2.warning_doublepay   ,
                        v_row2.user_id             ,
                        v_row2.recon_sent_date     ,
                        v_row2.medlytix_ins_type
                        )
                   ;
                END IF;


       END LOOP;

      RAISE NOTICE 'STOP INSERT OF MISSING RECON RECORD INTO batch_recon_t TABLE IF ANY';


      -- PROCESS B)
      RAISE NOTICE 'START PAYMENT CLEANING';
  
      UPDATE batch_recon_t
      SET 
      amount_paid   = NULL, 
      medlytix_note = 'automated msg: this record contained an identical payment which was set to 0' 

      FROM
        (SELECT
          distinct on (cust_id,hid,acctnum) 
          *
 
         FROM
        	(SELECT cust_id,
                	hid,
                	acctnum,
                	carrier_code,
                        amount_paid,
                        paid_date,
                	(case   when carrier_code = '00024'                                            THEN 1
                      		when carrier_code in ('00042','00092','00123','00196','00333','00029') THEN 2
                      		else                                                                        3 
                      		end) as ins_order
         	FROM batch_recon_t 
         	WHERE data_flag = 'C' and amount_paid > 0
         	) as a 

        ORDER by a.cust_id,a.hid,a.acctnum,a.ins_order

        ) as b 

      WHERE
        batch_recon_t.amount_paid > 0 AND
        batch_recon_t.data_flag = 'C' AND
        batch_recon_t.cust_id = b.cust_id AND
        batch_recon_t.hid = b.hid AND
        batch_recon_t.acctnum = b.acctnum AND
        batch_recon_t.carrier_code <> b.carrier_code AND
        batch_recon_t.amount_paid = b.amount_paid AND
        (batch_recon_t.paid_date IS NULL OR b.paid_date IS NULL OR batch_recon_t.paid_date = b.paid_date)
      ;


      RAISE NOTICE 'END PAYMENT CLEANING';





      -- PROCESS C)
      RAISE NOTICE 'START UPDATE OF validate_status';

      UPDATE batch_recon_t
      SET
        validate_status = (CASE WHEN amount_paid > 0 AND (paid_date IS NULL OR paid_date > first_delivery_date) THEN 'AC'
                                WHEN date_billed IS NOT NULL
                                     AND
                                     (
                                       date_rejected  IS NULL                      AND
                                       prior_ins_date IS NULL                      AND
                                       (amount_paid  IS NULL or amount_paid = 0)   AND
                                       paid_date     IS NULL                       AND
                                       note          IS NULL                       AND
                                       medlytix_note IS NULL             
                                     )                                                                                           THEN 'AO'
                                ELSE                                                                                                 NULL
                                END),
        medlytix_note = (CASE WHEN amount_paid > 0 AND (paid_date IS NOT NULL AND paid_date <= first_delivery_date)  
                                                   THEN 'automated msg: payment on or before delivery file date'
                              ELSE                      batch_recon_t.medlytix_note
                              END)
      ;

      RAISE NOTICE 'END UPDATE OF validate_status';


END IF;




---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-- UPDATING BATCH STATUS TABLE 

IF (v_data_flag = 'M') OR (v_data_flag = 'C') THEN
  SELECT INTO v_batch_recon_received count(*) from temp_batch_recon_t ;
  SELECT INTO v_status count(*)               from batch_recon_t ;
ELSE
  v_batch_recon_received := 0;
  v_status := 0;
END IF;


UPDATE batch_status_recon_t
SET
   temp_batch_recon_received  = v_batch_recon_received,
   batch_recon_inserted       = v_status
 WHERE
  (batch_status_recon_t.last_run_date = v_batch_run_date and batch_status_recon_t.cust_id = v_cust_id);


PERFORM ml_upd_batch_status_recon_new_f(v_cust_id,v_batch_run_date,1,2,'FINISH INSERT IN batch_recon_t');



  return v_status;
END;
$$ LANGUAGE plpgsql;
